if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getBit]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[getBit]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paddingMonth]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[paddingMonth]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JDataUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[JDataUpdate]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JSave_Data]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[JSave_Data]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JSave_Data_Nohour]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[JSave_Data_Nohour]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_bakHourStore]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_bakHourStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_repHourStore]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_repHourStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_logWriter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_logWriter]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_updateData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_updateData]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_updateHourData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_updateHourData]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_updateStoreData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_updateStoreData]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_updateTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_updateTest]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_writeItemLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[usp_writeItemLog]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zhejiang]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[zhejiang]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Acl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Acl]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_AlarmConfig]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_AlarmConfig]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_AlarmLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_AlarmLog]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Area]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Area]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_CheckOn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_CheckOn]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_CheckUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_CheckUser]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_CollectionFrqConfig]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_CollectionFrqConfig]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DayInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_DayInfo]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DayStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_DayStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Device]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Device]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_DeviceBak]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_DeviceBak]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Exchange]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Exchange]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FTP_Flag]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_FTP_Flag]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FTP_ItemID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_FTP_ItemID]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FTP_SiteID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_FTP_SiteID]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Filter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Filter]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_FunctionItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_FunctionItem]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_HistoryData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_HistoryData]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_HourStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_HourStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_HourStore_Bak]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_HourStore_Bak]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_HourStore_zhejiang]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_HourStore_zhejiang]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Item]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Item2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Item2]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_MinuteStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_MinuteStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_MinuteStore_bak]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_MinuteStore_bak]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_MonitorType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_MonitorType]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_MonthInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_MonthInfo]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_MonthStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_MonthStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_QualityType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_QualityType]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_QuaterStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_QuaterStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_RealData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_RealData]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Role]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Sample3G]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Sample3G]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Site]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Site]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SiteAllDevice]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_SiteAllDevice]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SiteItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_SiteItem]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SiteSystemDesc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_SiteSystemDesc]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SmsConfig]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_SmsConfig]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SystemStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_SystemStatus]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_SystemStatusDesc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_SystemStatusDesc]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_TroubleConfig]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_TroubleConfig]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_User]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_UserAlarmSmsLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_UserAlarmSmsLog]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_UserLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_UserLog]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_UserSmsLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_UserSmsLog]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Valley]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_Valley]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_WeekInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_WeekInfo]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_WeekStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_WeekStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_YearStore]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_YearStore]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_infoMedia]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_infoMedia]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_infoSender]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_infoSender]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_siteCamera]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_siteCamera]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_siteMAC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_siteMAC]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_task]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_task]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_userTask]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T_userTask]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_SmsPlan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[t_SmsPlan]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_datatype]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[t_datatype]GO//估计：角色-功能权限映射表CREATE TABLE [dbo].[T_Acl] (	[roleID] [int] NOT NULL ,	[functionItemID] [int] NOT NULL ) ON [PRIMARY]GO//系统维护-报警设置//1：一般； 2：严重；3： 紧急CREATE TABLE [dbo].[T_AlarmConfig] (	[ID] [int] IDENTITY (1, 1) NOT NULL ,	[alarmName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[sendTimes] [int] NOT NULL ) ON [PRIMARY]GO//报警信息，ID递增//未用到CREATE TABLE [dbo].[T_AlarmLog] (	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,	[siteID] [int] NOT NULL ,	[alarmClass] [int] NOT NULL ,	[readTime] [datetime] NOT NULL ,	[alarmSrcID] [int] NOT NULL ,	[alarmSrc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[alarmValue] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[logDesc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[sendTime] [datetime] NULL ,	[logType] [int] NOT NULL ,	[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//基础数据-区域信息CREATE TABLE [dbo].[T_Area] (	[ID] [int] NOT NULL ,	[province] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[city] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[district] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[postCode] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//不知用在何处//无数据CREATE TABLE [dbo].[T_CheckOn] (	[IntNo] [int] IDENTITY (1, 1) NOT NULL ,	[userID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[checkTime] [datetime] NULL ,	[siteMac] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//不知用在何处//无数据//估计：用户跟踪：上次登录站点，上次登录IP等CREATE TABLE [dbo].[T_CheckUser] (	[UserID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[UserLastSite] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,	[UserILastCheck] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[UserLastIP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,	[UserStatus] [int] NOT NULL ) ON [PRIMARY]GO//不知用在何处//估计：站点采集频率CREATE TABLE [dbo].[T_CollectionFrqConfig] (	[siteID] [int] NOT NULL ,	[interval] [bigint] NOT NULL ,	[T_CollectionFrqConfig_ID] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GO//每日报表//与T_DayStore关联CREATE TABLE [dbo].[T_DayInfo] (	[siteID] [int] NOT NULL ,	[genDate] [datetime] NOT NULL ,	[level] [int] NULL ,	[totalApi] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[pollute] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[status] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ) ON [PRIMARY]GO//每日报表中的具体数值//与T_DayInfo关联CREATE TABLE [dbo].[T_DayStore] (	[siteID] [int] NOT NULL ,	[genDate] [datetime] NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ) ON [PRIMARY]GO//基础数据-检测设备：静态信息//与 T_DeviceBak 关联CREATE TABLE [dbo].[T_Device] (	[MTypeID] [int] NOT NULL ,	[ID] [int] NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[imageURI] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[instructionCode] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[instructionName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//基础数据-检测设备-具体信息//与 T_Device 关联CREATE TABLE [dbo].[T_DeviceBak] (	[MTypeID] [int] NOT NULL ,	[ID] [int] IDENTITY (1, 1) NOT NULL ,	[deviceType] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[devicename] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[deviceDisplayName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[deviceImageUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[instructionCode] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[dWordSeq] [int] NULL ,	[bitSeq] [int] NULL ,	[statusValue] [float] NULL ,	[statDesc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[statImageUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[controllFlag] [int] NULL ,	[alarmFlag] [int] NOT NULL ,	[alarmContent] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readtime] [datetime] NULL ,	[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//不知用在何处//估计：与站点的交互指令//可能会用在实时监控中CREATE TABLE [dbo].[T_Exchange] (	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,	[siteID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[gprsID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[instructionCode] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[parameter] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[createTime] [datetime] NULL ,	[sentTime] [datetime] NULL ,	[status] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[result] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//不知用在何处//估计：与站点相关的FTP的flagCREATE TABLE [dbo].[T_FTP_Flag] (	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,	[flag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,	[status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//不知用在何处//估计：与设备相关的FTPCREATE TABLE [dbo].[T_FTP_ItemID] (	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,	[itemID] [int] NULL ,	[itemnum] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//不知用在何处CREATE TABLE [dbo].[T_FTP_SiteID] (	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,	[siteID] [int] NOT NULL ,	[siteNum] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//不知用在何处//估计：站点统计项目的过滤条件//可能还未用到CREATE TABLE [dbo].[T_Filter] (	[siteID] [int] NOT NULL ,	[itemID] [int] NOT NULL ,	[fMaxRange] [float] NOT NULL ,	[fMinRange] [float] NOT NULL ,	[T_Filter_ID] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GO//系统功能CREATE TABLE [dbo].[T_FunctionItem] (	[ID] [int] IDENTITY (1, 1) NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[url] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[level] [int] NOT NULL ,	[parentLevel] [int] NOT NULL ,	[imagePath] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[sortSeq] [int] NOT NULL ) ON [PRIMARY]GO//不知用在何处//估计：历史数据CREATE TABLE [dbo].[T_HistoryData] (	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,	[GPRSID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readTime] [datetime] NOT NULL ,	[itemID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[value] [numeric](10, 5) NOT NULL ,	[flag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//小时报表：与每日报表不同，每日报表分为T_DayInfo与T_DayStore两张表，这里是一张表//问题：这个应该是终端直接发送过来的，那么每日报表是直接发送过来的，还是基于小时报表经过统计之后的？若是统计，在什么时间点统计？CREATE TABLE [dbo].[T_HourStore] (	[siteID] [int] NOT NULL ,	[genTime] [datetime] NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ,	[T_HourStore_ID] [int] IDENTITY (1, 1) NOT NULL ,	[status] [int] NULL ) ON [PRIMARY]GO//备份//无数据CREATE TABLE [dbo].[T_HourStore_Bak] (	[siteID] [int] NOT NULL ,	[genTime] [datetime] NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ,	[T_HourStore_ID] [int] IDENTITY (1, 1) NOT NULL ,	[status] [int] NULL ) ON [PRIMARY]GO//浙江数据，需要最大值最小值，但无数据CREATE TABLE [dbo].[T_HourStore_zhejiang] (	[siteID] [int] NOT NULL ,	[genTime] [datetime] NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[maxvalue] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[maxflag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[minvalue] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[minflag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//基础数据-检测项目CREATE TABLE [dbo].[T_Item] (	[MTypeID] [int] NOT NULL ,	[ID] [int] NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[abrName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[nationalName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[eMaxValue] [float] NULL ,	[eMinValue] [float] NULL ,	[overMaxValue] [float] NULL ,	[overMinValue] [float] NULL ,	[standUnit] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[scale] [int] NULL ,	[maxRange] [float] NULL ,	[minRange] [float] NULL ) ON [PRIMARY]GO//估计：作为T_Item的abrName的参照表CREATE TABLE [dbo].[T_Item2] (	[ID] [int] NOT NULL ,	[abrName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//数据报表-分钟数据详细信息CREATE TABLE [dbo].[T_MinuteStore] (	[siteID] [int] NOT NULL ,	[genTime] [datetime] NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[filterFlag] [bit] NOT NULL ,	[T_MinuteStore_ID] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GO//估计：开发中的备份CREATE TABLE [dbo].[T_MinuteStore_bak] (	[siteID] [int] NOT NULL ,	[genTime] [datetime] NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[filterFlag] [bit] NOT NULL ,	[T_MinuteStore_ID] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GO//基础数据-监测类别//1：水检测； 2.。。CREATE TABLE [dbo].[T_MonitorType] (	[ID] [int] NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//数据报表-每月报表//估计：是在调用功能的时候才去统计该值CREATE TABLE [dbo].[T_MonthInfo] (	[siteID] [int] NOT NULL ,	[year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[level] [int] NULL ,	[totalApi] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[pollute] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[status] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ) ON [PRIMARY]GO//数据报表-每月报表-具体值CREATE TABLE [dbo].[T_MonthStore] (	[siteID] [int] NOT NULL ,	[year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[month] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ,	[T_MonthStore_ID] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GO//基础数据-质量类别//MTypeID: 监测类别（一般是1，水检测） ； itemID：检测的项目，与T_Item关联CREATE TABLE [dbo].[T_QualityType] (	[MTypeID] [int] NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[itemID] [int] NOT NULL ,	[MaxValue] [float] NULL ,	[MinValue] [float] NULL ,	[memo] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[level] [int] NULL ,	[T_QualityType_ID] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GO//数据报表-每季报表： 一张表展示CREATE TABLE [dbo].[T_QuaterStore] (	[siteID] [int] NOT NULL ,	[year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[quater] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ,	[T_QuaterStore_ID] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]GO//估计：实时传递过来的数据，经过处理后删除CREATE TABLE [dbo].[T_RealData] (	[ID] [bigint] IDENTITY (1, 1) NOT NULL ,	[siteID] [int] NOT NULL ,	[GPRSID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readTime] [datetime] NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [numeric](10, 5) NOT NULL ,	[flag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//权限管理-角色管理CREATE TABLE [dbo].[T_Role] (	[ID] [int] IDENTITY (1, 1) NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//未用到CREATE TABLE [dbo].[T_Sample3G] (	[siteMAC] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[SampleTime] [datetime] NOT NULL ,	[Sta] [int] NOT NULL ,	[siteName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//站点管理-站点信息CREATE TABLE [dbo].[T_Site] (	[MTypeID] [int] NOT NULL ,	[ID] [int] IDENTITY (1, 1) NOT NULL ,	[areaID] [int] NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[gprsID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[valley] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[waterSystem] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[alongSide] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[owner] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[gprsGEO] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[qualityLevel] [int] NULL ,	[contact] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[telephone] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[mobile] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[eMail] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[ipAddress] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[port] [int] NULL ,	[dialNumber] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,	[pstnid] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[image] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[pstnuser] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[pstnpassword] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[deviceMapUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[autoFilter] [bit] NULL ,	[freqMin] [int] NULL ,	[onlineStatus] [bit] NULL ,	[lastLoginTime] [datetime] NULL ,	[commMethod] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[onlineInfo] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[standrate] [float] NULL ,	[receiveravguser] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[receiverrealuser] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[ftpdatetime] [datetime] NULL ) ON [PRIMARY]GO//站点管理-设备信息CREATE TABLE [dbo].[T_SiteAllDevice] (	[ID] [int] IDENTITY (1, 1) NOT NULL ,	[siteID] [int] NOT NULL ,	[deviceType] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[deviceID] [int] NOT NULL ,	[devicename] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[deviceDisplayName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[deviceImageUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[instructionCode] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[dWordSeq] [int] NULL ,	[bitSeq] [int] NULL ,	[statusValue] [float] NULL ,	[statDesc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[statImageUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[controllFlag] [bit] NULL ,	[alarmFlag] [int] NOT NULL ,	[alarmContent] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readtime] [datetime] NULL ,	[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//站点管理-检测项目CREATE TABLE [dbo].[T_SiteItem] (	[siteID] [int] NOT NULL ,	[deviceID] [int] NULL ,	[itemID] [int] NOT NULL ,	[eMaxValue] [float] NULL ,	[eMinValue] [float] NULL ,	[overMaxValue] [float] NULL ,	[overMinValue] [float] NULL ,	[unit] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[scale] [int] NULL ,	[maxRange] [float] NULL ,	[minRange] [float] NULL ,	[factor] [float] NULL ,	[dataid] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[datafid] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[alarmFlag] [int] NULL ,	[alarmContent] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//站点管理-系统状态CREATE TABLE [dbo].[T_SiteSystemDesc] (	[ID] [int] IDENTITY (1, 1) NOT NULL ,	[siteID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[dWordSeq] [int] NOT NULL ,	[bitSeq] [int] NOT NULL ,	[flagValue] [int] NOT NULL ,	[sysCode] [int] NOT NULL ,	[alarmFlag] [int] NOT NULL ,	[alarmContent] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//短信息-短信设置CREATE TABLE [dbo].[T_SmsConfig] (	[provider] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[serviceNO] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[mobileNO] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[password] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[sendTimes] [int] NOT NULL ) ON [PRIMARY]GO//不知用在何处//估计：与T_SiteSystemDesc有点类似，CREATE TABLE [dbo].[T_SystemStatus] (	[siteID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[readTime] [datetime] NOT NULL ,	[dWordSeq] [int] NOT NULL ,	[wordValue] [bigint] NULL ) ON [PRIMARY]GO//系统维护-系统状态（参照表）CREATE TABLE [dbo].[T_SystemStatusDesc] (	[sysCode] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[descName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[imageUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//系统维护-故障级别CREATE TABLE [dbo].[T_TroubleConfig] (	[ID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[priority] [int] NOT NULL ) ON [PRIMARY]GO//用户表CREATE TABLE [dbo].[T_User] (	[ID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[password] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[roleID] [int] NOT NULL ,	[mobilePhone] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[mailAddress] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[sites] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [bit] NOT NULL ,	[smsFlag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[controllFlag] [bit] NOT NULL ,	[smtp] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,	[emailAccount] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,	[emailPassword] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//用户登录记录CREATE TABLE [dbo].[T_UserAlarmSmsLog] (	[siteid] [int] NOT NULL ,	[userid] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[logid] [bigint] NOT NULL ,	[readtime] [datetime] NULL ) ON [PRIMARY]GO//不知何用CREATE TABLE [dbo].[T_UserLog] (	[siteID] [int] NOT NULL ,	[userID] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[logID] [bigint] NOT NULL ,	[readTime] [datetime] NULL ) ON [PRIMARY]GO//短信息-短信维护CREATE TABLE [dbo].[T_UserSmsLog] (	[mobile] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[smsInfo] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[smsflag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,	[smsdatetime] [datetime] NULL ) ON [PRIMARY]GO//基础数据-流域/水系/断面CREATE TABLE [dbo].[T_Valley] (	[name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[waterSystem] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[alongSide] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO//数据报表-每周报表CREATE TABLE [dbo].[T_WeekInfo] (	[siteID] [int] NOT NULL ,	[year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[weekth] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[level] [int] NULL ,	[totalApi] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[pollute] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[status] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ) ON [PRIMARY]GO//数据报表-每周报表-详细CREATE TABLE [dbo].[T_WeekStore] (	[siteID] [int] NOT NULL ,	[year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[weekth] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ) ON [PRIMARY]GO//数据报表-每年报表： 一张表CREATE TABLE [dbo].[T_YearStore] (	[siteID] [int] NOT NULL ,	[year] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[itemID] [int] NOT NULL ,	[value] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[flag] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[readable] [bit] NOT NULL ,	[publishable] [bit] NOT NULL ) ON [PRIMARY]GO//基础数据-媒体信息CREATE TABLE [dbo].[T_infoMedia] (	[id] [int] NOT NULL ,	[mediaName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[email] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[contact] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[telephone] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[ftp] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[path] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,	[dataGroup] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//不知用在何处//估计：发送内容与信息CREATE TABLE [dbo].[T_infoSender] (	[id] [bigint] IDENTITY (1, 1) NOT NULL ,	[dataTime] [datetime] NOT NULL ,	[sendTime] [datetime] NOT NULL ,	[userid] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[dataGroup] [int] NOT NULL ,	[sendContent] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO//图像监控-站点镜头//还不能用CREATE TABLE [dbo].[T_siteCamera] (	[siteID] [int] NOT NULL ,	[siteDesc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[macAddress] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[ipaddress] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[historyImagePath] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[readTime] [datetime] NULL ) ON [PRIMARY]GO//站点管理-站点MACCREATE TABLE [dbo].[T_siteMAC] (	[siteMAC] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[siteName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,	[onLine] [datetime] NULL ) ON [PRIMARY]GO//计划任务-按站点CREATE TABLE [dbo].[T_task] (	[id] [bigint] IDENTITY (1, 1) NOT NULL ,	[siteID] [int] NOT NULL ,	[taskDesc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[creater] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[createTime] [datetime] NOT NULL ,	[receiver] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[taskType] [int] NULL ,	[interval] [int] NULL ,	[runTime] [datetime] NULL ,	[enableFlag] [bit] NULL ,	[tipBeforeDays] [int] NOT NULL ) ON [PRIMARY]GO//用户任务-按用户CREATE TABLE [dbo].[T_userTask] (	[id] [bigint] IDENTITY (1, 1) NOT NULL ,	[taskID] [bigint] NOT NULL ,	[userid] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[executeTime] [datetime] NOT NULL ,	[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO//短信息-短信定制CREATE TABLE [dbo].[t_SmsPlan] (	[Id] [int] IDENTITY (1, 1) NOT NULL ,	[Mobile] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[SmsType] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[Sites] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[MobileUserName] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[UserId] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[AppointTime] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,	[Memo] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,	[SendDate] [int] NULL ) ON [PRIMARY]GO//估计：数据类型//0:实时数据； 1：日均值CREATE TABLE [dbo].[t_datatype] (	[Id] [int] NOT NULL ,	[sdatatype] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOCREATE procedure JDataUpdate @nSiteID int,     @dMinDate datetime,     @dMaxDate datetime asdeclare @dMin datetime,@dMax datetime--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存日数据--删除日数据有可能重复的数据 select @dMin=Cast(DateName(YYYY,@dMinDate)+'-'+DateName(MM,@dMinDate)+'-'+DateName(DD,@dMinDate)+' '+'00:00:00' as Datetime), @dMax=Cast(DateName(YYYY,@dMaxDate)+'-'+DateName(MM,@dMaxDate)+'-'+DateName(DD,@dMaxDate)+' '+'23:59:59' as Datetime)delete T_DayStore from T_DayStore where SiteID=@nSiteID   and genDate>=@dMin and  genDate<=@dMax--创建临时小时数据表tHour,存储临时要处理的小时数据 Select SiteID,gentime,itemid,value,flag  Into #tHour From  T_HourStore Where SiteID=@nSiteID   and gentime>=@dMin and  genTime<@dMax--把所有时间变为无小时精度的数据  UPDATE #tHour SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+'00:00:00' as Datetime)--插入正常范围的平均值至小时数据表T_DayStore   Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tHour from #tHour, T_DayStore  where  #tHour.SiteID=T_DayStore.SiteID and #tHour.gentime=T_DayStore.genDate and #tHour.itemid=T_DayStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储周数据--删除周数据有可能重复的数据delete t_WeekStore  From  T_WeekStore Where SiteID=@nSiteID   and    ((T_WeekStore.[year]=datepart(year,@dMinDate) and T_WeekStore.[weekth]>= datepart(week,@dMinDate))    or  ( T_WeekStore.[year]>datepart(year,@dMinDate)  and T_WeekStore.[year]<datepart(year,@dMaxDate))      or  ( T_WeekStore.[year]=datepart(year,@dMaxDate)  and T_WeekStore.[weekth]<=datepart(week,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,gendate)=datepart(year,@dMinDate) and datepart(week,gendate)>= datepart(week,@dMinDate))    or  ( datepart(year,gendate)>datepart(year,@dMinDate)  and datepart(year,gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,gendate)=datepart(year,@dMaxDate)  and datepart(week,gendate)<=datepart(week,@dMaxDate)))--插入正常范围的平均值至月数据表T_weekStore   Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--删除 异常重复的数据 Delete #tDay from #tDay, T_WeekStore  where  #tDay.SiteID=T_WeekStore.SiteID and  DateName(YYYY,#tDay.genDate)=T_WeekStore.[year] and  Datepart(week,#tDay.genDate)=T_WeekStore.[weekth] and #tDay.itemid=T_WeekStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储月数据--删除月数据有可能重复的数据delete t_MonthStore  From  T_MonthStore Where SiteID=@nSiteID   and    ((T_MonthStore.[year]=datepart(year,@dMinDate) and T_MonthStore.[month]>= datepart(Month,@dMinDate))    or  ( T_MonthStore.[year]>datepart(year,@dMinDate)  and T_MonthStore.[year]<datepart(year,@dMaxDate))      or  ( T_MonthStore.[year]=datepart(year,@dMaxDate)  and T_MonthStore.[month]<=datepart(Month,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay2 From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,T_DayStore.gendate)=datepart(year,@dMinDate) and datepart(month,T_DayStore.gendate)>= datepart(month,@dMinDate))    or  ( datepart(year,T_DayStore.gendate)>datepart(year,@dMinDate)  and datepart(year,T_DayStore.gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,T_DayStore.gendate)=datepart(year,@dMaxDate)  and datepart(month,T_DayStore.gendate)<=datepart(month,@dMaxDate)))--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_MonthStore(SiteID,[year],[Month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(Month,#tDay2.genDate))--删除 异常重复的数据 Delete #tDay2 from #tDay2, T_MonthStore  where  #tDay2.SiteID=T_MonthStore.SiteID and  DateName(YYYY,#tDay2.genDate)=T_MonthStore.[year] and  Datepart(Month,#tDay2.genDate)=T_MonthStore.[month] and #tDay2.itemid=T_MonthStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_MonthStore(SiteID,[year],[month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(month,#tDay2.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储季数据--删除季数据有可能重复的数据delete t_QuaterStore  From  T_quaterStore Where (SiteID=@nSiteID)   and (T_QuaterStore.[year]>=datepart(year,@dMinDate) )  and  (T_QuaterStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],[quater]=0 ,itemid,value,flag  Into #tMonth From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)update #tMonth set [quater]=1 where ([month]=1 or  [month]=2 or [month]=3)update #tMonth set [quater]=2 where ([month]=4 or  [month]=5 or [month]=6)update #tMonth set [quater]=3 where ([month]=7 or  [month]=8 or [month]=9)update #tMonth set [quater]=4 where ([month]=10 or  [month]=11 or [month]=12)--插入正常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--删除 异常重复的数据 Delete #tMonth from #tMonth, T_QuaterStore  where  #tMonth.SiteID=T_QuaterStore.SiteID and #tMonth.[year]=T_QuaterStore.[year] and #tMonth.[quater]=T_QuaterStore.[quater] and  #tMonth.itemid=T_QuaterStore.itemid--插入异常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储年数据--删除年数据有可能重复的数据delete t_YearStore  From  T_YearStore Where (SiteID=@nSiteID)   and (T_YearStore.[year]>=datepart(year,@dMinDate) )  and  (T_YearStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],itemid,value,flag  Into #tMonth2 From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth2.[year])--删除 异常重复的数据 Delete #tMonth2 from #tMonth2, T_YearStore  where  #tMonth2.SiteID=T_YearStore.SiteID and  #tMonth2.[year]=T_YearStore.[year] and  #tMonth2.itemid=T_YearStore.itemid--插入异常范围的平均值至小时数据表T_HourStore Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth2.[year])GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE JSave_Data--add    @nSiteID int,    @dMinDate datetime,    @dMaxDate datetimeAS   declare @dMin datetime,@dMax datetime--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存小时数据--backup the dataexec p_bakHourStore @nSiteID,@dMinDate,@dMaxDate--删除小时数据有可能重复的数据delete T_HourStore from T_HourStore where SiteID=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDate--创建临时分钟数据表tMinute,存储临时要处理的分钟数据 Select SiteID,gentime,itemid,value,flag  Into #tMinute From  T_MinuteStore Where SiteID=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDate--把所有时间变为无分钟精度的数据  UPDATE #tMinute SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+DateName(HH,gentime)+':00:00' as Datetime)--插入正常范围的平均值至小时数据表T_HourStore   Insert    INTO T_HourStore(SiteID,genTime,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0  From #tMinute  WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tMinute from #tMinute, T_HourStore  where  #tMinute.SiteID=T_HourStore.SiteID and #tMinute.gentime=T_HourStore.gentime and #tMinute.itemid=T_HourStore.itemid--插入异常范围的平均值至小时数据表T_HourStore   Insert    INTO T_HourStore(SiteID,genTime,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0  From #tMinute  WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存日数据--删除日数据有可能重复的数据 select @dMin=Cast(DateName(YYYY,@dMinDate)+'-'+DateName(MM,@dMinDate)+'-'+DateName(DD,@dMinDate)+' '+'00:00:00' as Datetime), @dMax=Cast(DateName(YYYY,@dMaxDate)+'-'+DateName(MM,@dMaxDate)+'-'+DateName(DD,@dMaxDate)+' '+'23:59:59' as Datetime)delete T_DayStore from T_DayStore where SiteID=@nSiteID   and genDate>=@dMin and  genDate<=@dMax--创建临时小时数据表tHour,存储临时要处理的小时数据 Select SiteID,gentime,itemid,value,flag  Into #tHour From  T_HourStore Where SiteID=@nSiteID   and gentime>=@dMin and  genTime<@dMax--把所有时间变为无小时精度的数据  UPDATE #tHour SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+'00:00:00' as Datetime)--插入正常范围的平均值至小时数据表T_DayStore   Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tHour from #tHour, T_DayStore  where  #tHour.SiteID=T_DayStore.SiteID and #tHour.gentime=T_DayStore.genDate and #tHour.itemid=T_DayStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储周数据--删除周数据有可能重复的数据delete t_WeekStore  From  T_WeekStore Where SiteID=@nSiteID   and    ((T_WeekStore.[year]=datepart(year,@dMinDate) and T_WeekStore.[weekth]>= datepart(week,@dMinDate))    or  ( T_WeekStore.[year]>datepart(year,@dMinDate)  and T_WeekStore.[year]<datepart(year,@dMaxDate))      or  ( T_WeekStore.[year]=datepart(year,@dMaxDate)  and T_WeekStore.[weekth]<=datepart(week,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,gendate)=datepart(year,@dMinDate) and datepart(week,gendate)>= datepart(week,@dMinDate))    or  ( datepart(year,gendate)>datepart(year,@dMinDate)  and datepart(year,gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,gendate)=datepart(year,@dMaxDate)  and datepart(week,gendate)<=datepart(week,@dMaxDate)))--插入正常范围的平均值至月数据表T_weekStore   Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--删除 异常重复的数据 Delete #tDay from #tDay, T_WeekStore  where  #tDay.SiteID=T_WeekStore.SiteID and  DateName(YYYY,#tDay.genDate)=T_WeekStore.[year] and  Datepart(week,#tDay.genDate)=T_WeekStore.[weekth] and #tDay.itemid=T_WeekStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储月数据--删除月数据有可能重复的数据delete t_MonthStore  From  T_MonthStore Where SiteID=@nSiteID   and    ((T_MonthStore.[year]=datepart(year,@dMinDate) and T_MonthStore.[month]>= datepart(Month,@dMinDate))    or  ( T_MonthStore.[year]>datepart(year,@dMinDate)  and T_MonthStore.[year]<datepart(year,@dMaxDate))      or  ( T_MonthStore.[year]=datepart(year,@dMaxDate)  and T_MonthStore.[month]<=datepart(Month,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay2 From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,T_DayStore.gendate)=datepart(year,@dMinDate) and datepart(month,T_DayStore.gendate)>= datepart(month,@dMinDate))    or  ( datepart(year,T_DayStore.gendate)>datepart(year,@dMinDate)  and datepart(year,T_DayStore.gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,T_DayStore.gendate)=datepart(year,@dMaxDate)  and datepart(month,T_DayStore.gendate)<=datepart(month,@dMaxDate)))--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_MonthStore(SiteID,[year],[Month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(Month,#tDay2.genDate))--删除 异常重复的数据 Delete #tDay2 from #tDay2, T_MonthStore  where  #tDay2.SiteID=T_MonthStore.SiteID and  DateName(YYYY,#tDay2.genDate)=T_MonthStore.[year] and  Datepart(Month,#tDay2.genDate)=T_MonthStore.[month] and #tDay2.itemid=T_MonthStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_MonthStore(SiteID,[year],[month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(month,#tDay2.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储季数据--删除季数据有可能重复的数据delete t_QuaterStore  From  T_quaterStore Where (SiteID=@nSiteID)   and (T_QuaterStore.[year]>=datepart(year,@dMinDate) )  and  (T_QuaterStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],[quater]=0 ,itemid,value,flag  Into #tMonth From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)update #tMonth set [quater]=1 where ([month]=1 or  [month]=2 or [month]=3)update #tMonth set [quater]=2 where ([month]=4 or  [month]=5 or [month]=6)update #tMonth set [quater]=3 where ([month]=7 or  [month]=8 or [month]=9)update #tMonth set [quater]=4 where ([month]=10 or  [month]=11 or [month]=12)--插入正常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--删除 异常重复的数据 Delete #tMonth from #tMonth, T_QuaterStore  where  #tMonth.SiteID=T_QuaterStore.SiteID and #tMonth.[year]=T_QuaterStore.[year] and #tMonth.[quater]=T_QuaterStore.[quater] and  #tMonth.itemid=T_QuaterStore.itemid--插入异常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储年数据--删除年数据有可能重复的数据delete t_YearStore  From  T_YearStore Where (SiteID=@nSiteID)   and (T_YearStore.[year]>=datepart(year,@dMinDate) )  and  (T_YearStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],itemid,value,flag  Into #tMonth2 From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth2.[year])--删除 异常重复的数据 Delete #tMonth2 from #tMonth2, T_YearStore  where  #tMonth2.SiteID=T_YearStore.SiteID and  #tMonth2.[year]=T_YearStore.[year] and  #tMonth2.itemid=T_YearStore.itemid--插入异常范围的平均值至小时数据表T_HourStore Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth2.[year])--replace the dataexec p_repHourStore @nSiteID,@dMinDate,@dMaxDateGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOCREATE PROCEDURE JSave_Data_Nohour--add    @nSiteID int,    @dMinDate datetime,    @dMaxDate datetimeAS   declare @dMin datetime,@dMax datetime--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存日数据--删除日数据有可能重复的数据 select @dMin=Cast(DateName(YYYY,@dMinDate)+'-'+DateName(MM,@dMinDate)+'-'+DateName(DD,@dMinDate)+' '+'00:00:00' as Datetime), @dMax=Cast(DateName(YYYY,@dMaxDate)+'-'+DateName(MM,@dMaxDate)+'-'+DateName(DD,@dMaxDate)+' '+'23:59:59' as Datetime)delete T_DayStore from T_DayStore where SiteID=@nSiteID   and genDate>=@dMin and  genDate<=@dMax--创建临时小时数据表tHour,存储临时要处理的小时数据 Select SiteID,gentime,itemid,value,flag  Into #tHour From  T_HourStore Where SiteID=@nSiteID   and gentime>=@dMin and  genTime<@dMax--把所有时间变为无小时精度的数据  UPDATE #tHour SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+'00:00:00' as Datetime)--插入正常范围的平均值至小时数据表T_DayStore   Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tHour from #tHour, T_DayStore  where  #tHour.SiteID=T_DayStore.SiteID and #tHour.gentime=T_DayStore.genDate and #tHour.itemid=T_DayStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储周数据--删除周数据有可能重复的数据delete t_WeekStore  From  T_WeekStore Where SiteID=@nSiteID   and    ((T_WeekStore.[year]=datepart(year,@dMinDate) and T_WeekStore.[weekth]>= datepart(week,@dMinDate))    or  ( T_WeekStore.[year]>datepart(year,@dMinDate)  and T_WeekStore.[year]<datepart(year,@dMaxDate))      or  ( T_WeekStore.[year]=datepart(year,@dMaxDate)  and T_WeekStore.[weekth]<=datepart(week,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,gendate)=datepart(year,@dMinDate) and datepart(week,gendate)>= datepart(week,@dMinDate))    or  ( datepart(year,gendate)>datepart(year,@dMinDate)  and datepart(year,gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,gendate)=datepart(year,@dMaxDate)  and datepart(week,gendate)<=datepart(week,@dMaxDate)))--插入正常范围的平均值至月数据表T_weekStore   Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--删除 异常重复的数据 Delete #tDay from #tDay, T_WeekStore  where  #tDay.SiteID=T_WeekStore.SiteID and  DateName(YYYY,#tDay.genDate)=T_WeekStore.[year] and  Datepart(week,#tDay.genDate)=T_WeekStore.[weekth] and #tDay.itemid=T_WeekStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储月数据--删除月数据有可能重复的数据delete t_MonthStore  From  T_MonthStore Where SiteID=@nSiteID   and    ((T_MonthStore.[year]=datepart(year,@dMinDate) and T_MonthStore.[month]>= datepart(Month,@dMinDate))    or  ( T_MonthStore.[year]>datepart(year,@dMinDate)  and T_MonthStore.[year]<datepart(year,@dMaxDate))      or  ( T_MonthStore.[year]=datepart(year,@dMaxDate)  and T_MonthStore.[month]<=datepart(Month,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay2 From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,T_DayStore.gendate)=datepart(year,@dMinDate) and datepart(month,T_DayStore.gendate)>= datepart(month,@dMinDate))    or  ( datepart(year,T_DayStore.gendate)>datepart(year,@dMinDate)  and datepart(year,T_DayStore.gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,T_DayStore.gendate)=datepart(year,@dMaxDate)  and datepart(month,T_DayStore.gendate)<=datepart(month,@dMaxDate)))--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_MonthStore(SiteID,[year],[Month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(Month,#tDay2.genDate))--删除 异常重复的数据 Delete #tDay2 from #tDay2, T_MonthStore  where  #tDay2.SiteID=T_MonthStore.SiteID and  DateName(YYYY,#tDay2.genDate)=T_MonthStore.[year] and  Datepart(Month,#tDay2.genDate)=T_MonthStore.[month] and #tDay2.itemid=T_MonthStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_MonthStore(SiteID,[year],[month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(month,#tDay2.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储季数据--删除季数据有可能重复的数据delete t_QuaterStore  From  T_quaterStore Where (SiteID=@nSiteID)   and (T_QuaterStore.[year]>=datepart(year,@dMinDate) )  and  (T_QuaterStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],[quater]=0 ,itemid,value,flag  Into #tMonth From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)update #tMonth set [quater]=1 where ([month]=1 or  [month]=2 or [month]=3)update #tMonth set [quater]=2 where ([month]=4 or  [month]=5 or [month]=6)update #tMonth set [quater]=3 where ([month]=7 or  [month]=8 or [month]=9)update #tMonth set [quater]=4 where ([month]=10 or  [month]=11 or [month]=12)--插入正常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--删除 异常重复的数据 Delete #tMonth from #tMonth, T_QuaterStore  where  #tMonth.SiteID=T_QuaterStore.SiteID and #tMonth.[year]=T_QuaterStore.[year] and #tMonth.[quater]=T_QuaterStore.[quater] and  #tMonth.itemid=T_QuaterStore.itemid--插入异常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储年数据--删除年数据有可能重复的数据delete t_YearStore  From  T_YearStore Where (SiteID=@nSiteID)   and (T_YearStore.[year]>=datepart(year,@dMinDate) )  and  (T_YearStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],itemid,value,flag  Into #tMonth2 From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth2.[year])--删除 异常重复的数据 Delete #tMonth2 from #tMonth2, T_YearStore  where  #tMonth2.SiteID=T_YearStore.SiteID and  #tMonth2.[year]=T_YearStore.[year] and  #tMonth2.itemid=T_YearStore.itemid--插入异常范围的平均值至小时数据表T_HourStore Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth2.[year])GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO-- create procedure to backup hourstore manual data to backup hourstore table --CREATE PROCEDURE [dbo].[p_bakHourStore]@nSiteID int,@dMinDate datetime,@dMaxDate datetimeASbegin-- delete all the data from T_HourStore_Bak -- delete from T_hourStore_bak-- insert into the manul data to T_HourStore_Bak from T_HourStore --insert into T_HourStore_bak (SiteID,gentime,itemid,value,flag,status,readable,publishable) (select SiteID,gentime,itemid,value,flag,status,readable,publishable from T_HourStore where T_HourStore.SiteID=@nSiteID and T_HourStore.gentime>=@dMinDate and  T_HourStore.genTime<=@dMaxDate and T_HourStore.status=1)endGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO-- create produre to replace hourstore with backup hourstore data --create procedure [dbo].[p_repHourStore]@nSiteID int,@dMinDate datetime,@dMaxDate datetimeASbegin-- drop the calculate data from t_HourStore ---- delete from t_hourstore where status!=1 and siteId=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDatedelete from t_hourstore where t_hourstore_id in (select a.t_hourstore_id from t_hourstore a,t_hourstore_bak b where a.siteid=b.siteid and a.gentime=b.gentime and a.itemid=b.itemid and a.status!=1)-- init the data from the backup --insert into T_HourStore (SiteID,gentime,itemid,value,flag,status,readable,publishable) (select SiteID,gentime,itemid,value,flag,status,readable,publishable from t_hourstore_bak)endGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE usp_logWriter--add   -- @nSiteID int,    --@dMinDate datetime,    --@dMaxDate datetimeAS  declare @theID bigint,@theWordValue int,@theBitSeq int,@theStatusValue intDECLARE deviceLog_cursor CURSOR FOR select T_SiteAllDevice.id,wordValue,bitSeq,statusValue from T_SiteAllDevice,T_SystemStatus where alarmFlag > 0 and T_SiteAllDevice.siteid = T_SystemStatus.siteid and T_SiteAllDevice.dwordSeq = T_SystemStatus.dwordSeq   and (DATEDIFF(day, t_systemstatus.readTime, GETDATE()) <1)DECLARE systemLog_cursor CURSOR FOR select id,wordValue,bitseq,flagValue from T_SiteSystemDesc,T_SystemStatusDesc,t_systemStatus where alarmFlag > 0  and T_SiteSystemDesc.syscode = T_SystemStatusDesc.syscode and T_SiteSystemDesc.siteid = t_systemStatus.siteid and T_SiteSystemDesc.dwordSeq = t_systemStatus.dwordSeq    and (DATEDIFF(day, t_systemstatus.readTime, GETDATE()) <1)open deviceLog_cursorFETCH NEXT FROM deviceLog_cursor INTO @theID, @theWordValue, @theBitSeq,@theStatusValueWHILE @@FETCH_STATUS = 0BEGIN	if (@theStatusValue= dbo.getBit(@theWordValue,@theBitSeq)) --是否是写入日志		begin	    	if (select count(t_alarmlog.id) from t_alarmlog,T_SiteAllDevice where T_SiteAllDevice.id = @theID and logType = 2 and T_SiteAllDevice.siteid = t_alarmlog.siteid and				T_SiteAllDevice.deviceID = t_alarmlog.alarmSrcId  and (DATEDIFF(day, t_alarmlog.readTime, GETDATE()) <1)) = 0  --是否已存在于日志表中			begin				insert into T_AlarmLog(siteid,alarmClass,readtime,alarmSrcID,alarmSrc,alarmValue,logDesc,logtype) select siteid,alarmFlag,getdate(),deviceId,deviceName,statusValue,deviceName+statDesc,2 from T_SiteAllDevice where id = @theID;			end		endFETCH NEXT FROM deviceLog_cursor INTO @theID, @theWordValue, @theBitSeq,@theStatusValueENDCLOSE deviceLog_cursorDEALLOCATE deviceLog_cursoropen systemLog_cursorFETCH NEXT FROM systemLog_cursor INTO @theID, @theWordValue, @theBitSeq,@theStatusValueWHILE @@FETCH_STATUS = 0BEGIN	if (@theStatusValue= dbo.getBit(@theWordValue,@theBitSeq)) --是否是写入日志		begin	    	if (select count(t_alarmLog.id) from t_alarmLog,T_SiteSystemDesc where T_SiteSystemDesc.id = @theID and logType = 3 and t_alarmLog.siteid = T_SiteSystemDesc.siteid  and  				t_alarmLog.alarmSrcId = T_SiteSystemDesc.syscode  and (DATEDIFF(day, t_alarmlog.readTime, GETDATE()) <1)) = 0  --是否已存在于日志表中			begin				insert into T_AlarmLog(siteid,alarmClass,readtime,alarmSrcID,alarmSrc,alarmValue,logDesc,logtype) select siteid,alarmFlag,getdate(),T_SiteSystemDesc.syscode,descName,flagValue,descName,3 from T_SiteSystemDesc,T_SystemStatusDesc where T_SiteSystemDesc.id = @theID and T_SystemStatusDesc.syscode = T_SiteSystemDesc.syscode			end		endFETCH NEXT FROM systemLog_cursor INTO @theID, @theWordValue, @theBitSeq,@theStatusValueENDCLOSE systemLog_cursorDEALLOCATE systemLog_cursorexecute usp_writeItemLogGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE usp_updateData--add    @nSiteID int,    @dMinDate datetime,    @dMaxDate datetimeAS   declare @dMin datetime,@dMax datetime--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存小时数据--删除小时数据有可能重复的数据delete T_HourStore from T_HourStore where SiteID=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDate--创建临时分钟数据表tMinute,存储临时要处理的分钟数据 Select SiteID,gentime,itemid,value,flag  Into #tMinute From  T_MinuteStore Where SiteID=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDate--把所有时间变为无分钟精度的数据  UPDATE #tMinute SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+DateName(HH,gentime)+':00:00' as Datetime)--插入正常范围的平均值至小时数据表T_HourStore   Insert    INTO T_HourStore(SiteID,genTime,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0  From #tMinute  WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tMinute from #tMinute, T_HourStore  where  #tMinute.SiteID=T_HourStore.SiteID and #tMinute.gentime=T_HourStore.gentime and #tMinute.itemid=T_HourStore.itemid--插入异常范围的平均值至小时数据表T_HourStore   Insert    INTO T_HourStore(SiteID,genTime,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0  From #tMinute  WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存日数据--删除日数据有可能重复的数据 select @dMin=Cast(DateName(YYYY,@dMinDate)+'-'+DateName(MM,@dMinDate)+'-'+DateName(DD,@dMinDate)+' '+'00:00:00' as Datetime), @dMax=Cast(DateName(YYYY,@dMaxDate)+'-'+DateName(MM,@dMaxDate)+'-'+DateName(DD,@dMaxDate)+' '+'23:59:59' as Datetime)delete T_DayStore from T_DayStore where SiteID=@nSiteID   and genDate>=@dMin and  genDate<=@dMax--创建临时小时数据表tHour,存储临时要处理的小时数据 Select SiteID,gentime,itemid,value,flag  Into #tHour From  T_HourStore Where SiteID=@nSiteID   and gentime>=@dMin and  genTime<@dMax--把所有时间变为无小时精度的数据  UPDATE #tHour SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+'00:00:00' as Datetime)--插入正常范围的平均值至小时数据表T_DayStore   Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tHour from #tHour, T_DayStore  where  #tHour.SiteID=T_DayStore.SiteID and #tHour.gentime=T_DayStore.genDate and #tHour.itemid=T_DayStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_DayStore(SiteID,genDate,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0  From #tHour WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储周数据--删除周数据有可能重复的数据delete t_WeekStore  From  T_WeekStore Where SiteID=@nSiteID   and    ((T_WeekStore.[year]=datepart(year,@dMinDate) and T_WeekStore.[weekth]>= datepart(week,@dMinDate))    or  ( T_WeekStore.[year]>datepart(year,@dMinDate)  and T_WeekStore.[year]<datepart(year,@dMaxDate))      or  ( T_WeekStore.[year]=datepart(year,@dMaxDate)  and T_WeekStore.[weekth]<=datepart(week,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,gendate)=datepart(year,@dMinDate) and datepart(week,gendate)>= datepart(week,@dMinDate))    or  ( datepart(year,gendate)>datepart(year,@dMinDate)  and datepart(year,gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,gendate)=datepart(year,@dMaxDate)  and datepart(week,gendate)<=datepart(week,@dMaxDate)))--插入正常范围的平均值至月数据表T_weekStore   Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--删除 异常重复的数据 Delete #tDay from #tDay, T_WeekStore  where  #tDay.SiteID=T_WeekStore.SiteID and  DateName(YYYY,#tDay.genDate)=T_WeekStore.[year] and  Datepart(week,#tDay.genDate)=T_WeekStore.[weekth] and #tDay.itemid=T_WeekStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储月数据--删除月数据有可能重复的数据delete t_MonthStore  From  T_MonthStore Where SiteID=@nSiteID   and    ((T_MonthStore.[year]=datepart(year,@dMinDate) and T_MonthStore.[month]>= datepart(Month,@dMinDate))    or  ( T_MonthStore.[year]>datepart(year,@dMinDate)  and T_MonthStore.[year]<datepart(year,@dMaxDate))      or  ( T_MonthStore.[year]=datepart(year,@dMaxDate)  and T_MonthStore.[month]<=datepart(Month,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay2 From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,T_DayStore.gendate)=datepart(year,@dMinDate) and datepart(month,T_DayStore.gendate)>= datepart(month,@dMinDate))    or  ( datepart(year,T_DayStore.gendate)>datepart(year,@dMinDate)  and datepart(year,T_DayStore.gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,T_DayStore.gendate)=datepart(year,@dMaxDate)  and datepart(month,T_DayStore.gendate)<=datepart(month,@dMaxDate)))--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_MonthStore(SiteID,[year],[Month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(Month,#tDay2.genDate))--删除 异常重复的数据 Delete #tDay2 from #tDay2, T_MonthStore  where  #tDay2.SiteID=T_MonthStore.SiteID and  DateName(YYYY,#tDay2.genDate)=T_MonthStore.[year] and  Datepart(Month,#tDay2.genDate)=T_MonthStore.[month] and #tDay2.itemid=T_MonthStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_MonthStore(SiteID,[year],[month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(month,#tDay2.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储季数据--删除季数据有可能重复的数据delete t_QuaterStore  From  T_quaterStore Where (SiteID=@nSiteID)   and (T_QuaterStore.[year]>=datepart(year,@dMinDate) )  and  (T_QuaterStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],[quater]=0 ,itemid,value,flag  Into #tMonth From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)update #tMonth set [quater]=1 where ([month]=1 or  [month]=2 or [month]=3)update #tMonth set [quater]=2 where ([month]=4 or  [month]=5 or [month]=6)update #tMonth set [quater]=3 where ([month]=7 or  [month]=8 or [month]=9)update #tMonth set [quater]=4 where ([month]=10 or  [month]=11 or [month]=12)--插入正常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--删除 异常重复的数据 Delete #tMonth from #tMonth, T_QuaterStore  where  #tMonth.SiteID=T_QuaterStore.SiteID and #tMonth.[year]=T_QuaterStore.[year] and #tMonth.[quater]=T_QuaterStore.[quater] and  #tMonth.itemid=T_QuaterStore.itemid--插入异常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储年数据--删除年数据有可能重复的数据delete t_YearStore  From  T_YearStore Where (SiteID=@nSiteID)   and (T_YearStore.[year]>=datepart(year,@dMinDate) )  and  (T_YearStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],itemid,value,flag  Into #tMonth2 From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth2.[year])--删除 异常重复的数据 Delete #tMonth2 from #tMonth2, T_YearStore  where  #tMonth2.SiteID=T_YearStore.SiteID and  #tMonth2.[year]=T_YearStore.[year] and  #tMonth2.itemid=T_YearStore.itemid--插入异常范围的平均值至小时数据表T_HourStore Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth2.[year])GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOCREATE PROCEDURE usp_updateHourData--add    @nSiteID int,    @dMinDate datetime,    @dMaxDate datetimeAS   declare @dMin datetime,@dMax datetime--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存小时数据--删除小时数据有可能重复的数据delete T_HourStore from T_HourStore where SiteID=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDate--创建临时分钟数据表tMinute,存储临时要处理的分钟数据 Select SiteID,gentime,itemid,value,flag  Into #tMinute From  T_MinuteStore Where SiteID=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDate--把所有时间变为无分钟精度的数据  UPDATE #tMinute SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+DateName(HH,gentime)+':00:00' as Datetime)--插入正常范围的平均值至小时数据表T_HourStore   Insert    INTO T_HourStore(SiteID,genTime,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0  From #tMinute  WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tMinute from #tMinute, T_HourStore  where  #tMinute.SiteID=T_HourStore.SiteID and #tMinute.gentime=T_HourStore.gentime and #tMinute.itemid=T_HourStore.itemid--插入异常范围的平均值至小时数据表T_HourStore   Insert    INTO T_HourStore(SiteID,genTime,itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0  From #tMinute  WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS OFF GOCREATE PROCEDURE usp_updateStoreData--add    @nSiteID int,    @dMinDate datetime,    @dMaxDate datetimeAS  --////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储周数据--删除周数据有可能重复的数据delete t_WeekStore  From  T_WeekStore Where SiteID=@nSiteID   and    ((T_WeekStore.[year]=datepart(year,@dMinDate) and T_WeekStore.[weekth]>= datepart(week,@dMinDate))    or  ( T_WeekStore.[year]>datepart(year,@dMinDate)  and T_WeekStore.[year]<datepart(year,@dMaxDate))      or  ( T_WeekStore.[year]=datepart(year,@dMaxDate)  and T_WeekStore.[weekth]<=datepart(week,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,gendate)=datepart(year,@dMinDate) and datepart(week,gendate)>= datepart(week,@dMinDate))    or  ( datepart(year,gendate)>datepart(year,@dMinDate)  and datepart(year,gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,gendate)=datepart(year,@dMaxDate)  and datepart(week,gendate)<=datepart(week,@dMaxDate)))--插入正常范围的平均值至月数据表T_weekStore   Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--删除 异常重复的数据 Delete #tDay from #tDay, T_WeekStore  where  #tDay.SiteID=T_WeekStore.SiteID and  DateName(YYYY,#tDay.genDate)=T_WeekStore.[year] and  Datepart(week,#tDay.genDate)=T_WeekStore.[weekth] and #tDay.itemid=T_WeekStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储月数据--删除月数据有可能重复的数据delete t_MonthStore  From  T_MonthStore Where SiteID=@nSiteID   and    ((T_MonthStore.[year]=datepart(year,@dMinDate) and T_MonthStore.[month]>= datepart(Month,@dMinDate))    or  ( T_MonthStore.[year]>datepart(year,@dMinDate)  and T_MonthStore.[year]<datepart(year,@dMaxDate))      or  ( T_MonthStore.[year]=datepart(year,@dMaxDate)  and T_MonthStore.[month]<=datepart(Month,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay2 From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,T_DayStore.gendate)=datepart(year,@dMinDate) and datepart(month,T_DayStore.gendate)>= datepart(month,@dMinDate))    or  ( datepart(year,T_DayStore.gendate)>datepart(year,@dMinDate)  and datepart(year,T_DayStore.gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,T_DayStore.gendate)=datepart(year,@dMaxDate)  and datepart(month,T_DayStore.gendate)<=datepart(month,@dMaxDate)))--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_MonthStore(SiteID,[year],[Month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(Month,#tDay2.genDate))--删除 异常重复的数据 Delete #tDay2 from #tDay2, T_MonthStore  where  #tDay2.SiteID=T_MonthStore.SiteID and  DateName(YYYY,#tDay2.genDate)=T_MonthStore.[year] and  Datepart(Month,#tDay2.genDate)=T_MonthStore.[month] and #tDay2.itemid=T_MonthStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_MonthStore(SiteID,[year],[month],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay2.gendate),[month]=Datepart(month,#tDay2.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay2.gendate),Datepart(month,#tDay2.genDate))--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储季数据--删除季数据有可能重复的数据delete t_QuaterStore  From  T_quaterStore Where (SiteID=@nSiteID)   and (T_QuaterStore.[year]>=datepart(year,@dMinDate) )  and  (T_QuaterStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],[quater]=0 ,itemid,value,flag  Into #tMonth From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)update #tMonth set [quater]=1 where ([month]=1 or  [month]=2 or [month]=3)update #tMonth set [quater]=2 where ([month]=4 or  [month]=5 or [month]=6)update #tMonth set [quater]=3 where ([month]=7 or  [month]=8 or [month]=9)update #tMonth set [quater]=4 where ([month]=10 or  [month]=11 or [month]=12)--插入正常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--删除 异常重复的数据 Delete #tMonth from #tMonth, T_QuaterStore  where  #tMonth.SiteID=T_QuaterStore.SiteID and #tMonth.[year]=T_QuaterStore.[year] and #tMonth.[quater]=T_QuaterStore.[quater] and  #tMonth.itemid=T_QuaterStore.itemid--插入异常范围的平均值至季数据表T_quarterStore   Insert    INTO T_QuaterStore(SiteID,[year],[quater],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth.[year],[quater]=#tMonth.[quater],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth.[year],#tMonth.[quater])--////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储年数据--删除年数据有可能重复的数据delete t_YearStore  From  T_YearStore Where (SiteID=@nSiteID)   and (T_YearStore.[year]>=datepart(year,@dMinDate) )  and  (T_YearStore.[year]<=datepart(year,@dMaxDate))  --创建临时月数据表tMonth,存储临时要处理的月数据Select SiteID,[year],[month],itemid,value,flag  Into #tMonth2 From  T_MonthStore Where SiteID=@nSiteID   and   T_MonthStore.[year]>=datepart(year,@dMinDate)  and T_MonthStore.[year]<=datepart(year,@dMaxDate)--插入正常范围的平均值至月数据表T_MonthStore   Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,#tMonth2.[year])--删除 异常重复的数据 Delete #tMonth2 from #tMonth2, T_YearStore  where  #tMonth2.SiteID=T_YearStore.SiteID and  #tMonth2.[year]=T_YearStore.[year] and  #tMonth2.itemid=T_YearStore.itemid--插入异常范围的平均值至小时数据表T_HourStore Insert    INTO T_YearStore(SiteID,[year],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=#tMonth2.[year],itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tMonth2 WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,#tMonth2.[year])GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE usp_updateTest--add    @nSiteID int,    @dMinDate datetime,    @dMaxDate datetimeAS  --////////////////////////////////////////////////////////////////////////////////////////////////////////////////存储周数据--删除周数据有可能重复的数据delete t_WeekStore  From  T_WeekStore Where SiteID=@nSiteID   and    ((T_WeekStore.[year]=datepart(year,@dMinDate) and T_WeekStore.[weekth]>= datepart(week,@dMinDate))    or  ( T_WeekStore.[year]>datepart(year,@dMinDate)  and T_WeekStore.[year]<datepart(year,@dMaxDate))      or  ( T_WeekStore.[year]=datepart(year,@dMaxDate)  and T_WeekStore.[weekth]<=datepart(week,@dMaxDate)))--创建临时日数据表tDay,存储临时要处理的日数据Select SiteID,gendate,itemid,value,flag  Into #tDay From  T_DayStore Where SiteID=@nSiteID   and    ((datepart(year,gendate)=datepart(year,@dMinDate) and datepart(week,gendate)>= datepart(week,@dMinDate))    or  ( datepart(year,gendate)>datepart(year,@dMinDate)  and datepart(year,gendate)<datepart(year,@dMaxDate))      or  ( datepart(year,gendate)=datepart(year,@dMaxDate)  and datepart(week,gendate)<=datepart(week,@dMaxDate)))--插入正常范围的平均值至月数据表T_weekStore   Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))--删除 异常重复的数据 Delete #tDay from #tDay, T_WeekStore  where  #tDay.SiteID=T_WeekStore.SiteID and  DateName(YYYY,#tDay.genDate)=T_WeekStore.[year] and  Datepart(week,#tDay.genDate)=T_WeekStore.[weekth] and #tDay.itemid=T_WeekStore.itemid--插入异常范围的平均值至小时数据表T_HourStore  Insert    INTO T_WeekStore(SiteID,[year],[weekth],itemid,value,flag,readable,publishable)     (select SiteID=@nSiteID,[year]=DateName(YYYY,#tDay.gendate),[weekth]=Datepart(week,#tDay.genDate),itemid,value=avg(cast(value as float)),flag='p',readble=0,publishable=0   From #tDay WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,DateName(YYYY,#tDay.gendate),Datepart(week,#tDay.genDate))GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE usp_writeItemLogAS  DECLARE @site_id int, @item_id int,@item_name varchar(255),   @alarm_class int,@alarm_value numeric(9,3), @readtime datetimeDECLARE writeItem_cursor CURSOR FOR  Select t_realdata.siteid,t_realdata.itemid,t_item.name,t_siteitem.alarmFlag,	t_realdata.value,t_realdata.readtime From t_realdata,t_siteitem,t_item  Where t_realdata.siteid = t_siteitem.siteid and t_siteitem.itemid=t_item.id        and t_realdata.itemid = t_siteitem.itemid and alarmFlag > 0        and (value NOT BETWEEN  t_siteitem.eMinvalue AND t_siteitem.emaxValue) and  (DATEDIFF(day, t_realdata.readTime, GETDATE()) <1)OPEN writeItem_cursorFETCH NEXT FROM writeItem_cursor INTO @site_id,@item_id,@item_name,@alarm_class,@alarm_value,@readtimeWHILE @@FETCH_STATUS = 0BEGIN  	--logType=1为项目报警(这里只用1)	--logType=2为设备报警	--logType=3为系统报警       IF (SELECT count(id) FROM T_AlarmLog WHERE logType =1 AND  siteID=@site_id and alarmSrcID=@item_id  and (DATEDIFF(day, t_alarmlog.readTime, GETDATE()) <1))=0	BEGIN	   	   INSERT INTO T_AlarmLog(siteID,alarmSrcID,alarmSrc,alarmClass,alarmValue,logDesc,readTime,logType) values(@site_id,@item_id,@item_name,@alarm_class,@alarm_value,@item_name+','+cast(@alarm_value as varchar),@readtime,1)   	END      FETCH NEXT FROM writeItem_cursor    INTO @site_id,@item_id,@item_name,@alarm_class,@alarm_value,@readtimeENDCLOSE writeItem_cursorDEALLOCATE writeItem_cursorGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GOCREATE PROCEDURE  zhejiang  --add    @nSiteID int,    @dMinDate datetime,    @dMaxDate datetimeAS  --////////////////////////////////////////////////////////////////////////////////////////////////////////////////存小时数据--删除小时数据有可能重复的数据delete T_HourStore_zhejiang from T_HourStore_zhejiang --创建临时分钟数据表tMinute,存储临时要处理的分钟数据 Select SiteID,gentime,itemid,value,flag  Into #tMinutes From  T_MinuteStore Where SiteID=@nSiteID and gentime>=@dMinDate and  genTime<=@dMaxDate--把所有时间变为无分钟精度的数据  UPDATE #tMinutes SET gentime=Cast(DateName(YYYY,gentime)+'-'+DateName(MM,gentime)+'-'+DateName(DD,gentime)+' '+DateName(HH,gentime)+':00:00' as Datetime)--插入正常范围的平均值,最大值至T_HourStore_zhejiang   Insert    INTO T_HourStore_zhejiang(SiteID,genTime,itemid,value,flag,maxvalue,maxflag,minvalue,minflag)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='0',maxvalue=max(cast(value as float)),maxflag='0',minvalue=min(cast(value as float)),minflag='0'  From #tMinutes  WHERE (SiteID=@nSiteID) and (Flag='')  GROUP BY itemID,gentime)--删除 异常重复的数据 Delete #tMinutes from #tMinutes, T_HourStore_zhejiang  where  #tMinutes.SiteID=T_HourStore_zhejiang.SiteID and #tMinutes.gentime=T_HourStore_zhejiang.gentime and #tMinutes.itemid=T_HourStore_zhejiang.itemid--插入异常范围的平均值至小时数据表T_HourStore   Insert    INTO T_HourStore_zhejiang(SiteID,genTime,itemid,value,flag,maxvalue,maxflag,minvalue,minflag)     (select SiteID=@nSiteID,gentime,itemid,value=avg(cast(value as float)),flag='8' ,maxvalue=max(cast(value as float)),maxflag='8' ,minvalue=min(cast(value as float)),minflag='8'  From #tMinutes  WHERE (SiteID=@nSiteID) and (Flag<>'')  GROUP BY itemID,gentime)GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOCREATE FUNCTION getBit   (@theInt bigint,@pos bigint)RETURNS bitASBEGIN	   return (@theInt & power(2,@pos - 1))ENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOCREATE FUNCTION paddingMonth   (@singleMonth varchar(2) )RETURNS varchar(2)ASBEGIN	if  len(@singleMonth) = 1	   RETURN ( '0'+@singleMonth )	 RETURN ( @singleMonth )ENDGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO